﻿using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;

namespace TP_02.Models
{

    public class GameCategory
    {

        [Key]
        public int Id { get; set; }

        [Required]
        public string Name { get; set; }

        [Required]
        public string LongName { get; set; }

        [Required]
        public bool Multiplayer { get; set; }

        [Required]
        public bool Online { get; set; }

        public static List<GameCategory> GetList()
        {
            using (var cnx = new SqlConnection(
                    ConfigurationManager.ConnectionStrings["TP_02"].ConnectionString))
            {

                const string requete = "SELECT * FROM [GameCategory]";
                var cmd = new SqlCommand(requete, cnx) { CommandType = CommandType.Text };

                try
                {
                    cnx.Open();
                    var gameCategories = new List<GameCategory>();
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var gameCategory = new GameCategory
                            {
                                Id = (int)reader["Id"],
                                Name = (string)reader["Name"],
                                LongName = (string)reader["LongName"],
                                Multiplayer = (bool)reader["Multiplayer"],
                                Online = (bool)reader["Online"]
                            };
                            gameCategories.Add(gameCategory);
                        }
                    }
                    return gameCategories;
                }
                catch (Exception e)
                {
                    Debug.WriteLine(e.Message);
                    Debug.WriteLine(e.StackTrace);
                    return null;
                }
            }
        }

        public static GameCategory FindOne(int id)
        {
            using (var cnx = new SqlConnection(
                    ConfigurationManager.ConnectionStrings["TP_02"].ConnectionString))
            {

                var requete = "SELECT * FROM [GameCategory] WHERE Id = " + id;
                var cmd = new SqlCommand(requete, cnx) { CommandType = CommandType.Text };

                try
                {
                    cnx.Open();
                    GameCategory gameCategory = null;
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            gameCategory = new GameCategory
                            {
                                Id = (int)reader["Id"],
                                Name = (string)reader["Name"],
                                LongName = (string)reader["LongName"],
                                Multiplayer = (bool)reader["Multiplayer"],
                                Online = (bool)reader["Online"]
                            };
                        }
                    }
                    return gameCategory;
                }
                catch (Exception e)
                {
                    Debug.WriteLine(e.Message);
                    Debug.WriteLine(e.StackTrace);
                    return null;
                }
            }
        }

        public static bool Destroy(int id)
        {
            using (var cnx = new SqlConnection(
                    ConfigurationManager.ConnectionStrings["TP_02"].ConnectionString))
            {

                var requete = "DELETE FROM [GameCategory] WHERE Id = " + id;
                var cmd = new SqlCommand(requete, cnx) { CommandType = CommandType.Text };

                try
                {
                    cnx.Open();
                    cmd.ExecuteNonQuery();
                    cnx.Close();
                    return true;
                }
                catch (Exception e)
                {
                    Debug.WriteLine(e.Message);
                    Debug.WriteLine(e.StackTrace);
                    return false;
                }
            }
        }

        public bool SaveAsNew()
        {
            const string requete = "INSERT INTO [GameCategory] (Name, LongName, Multiplayer,"
                                   + "Online) VALUES (@Name, @LongName,"
                                   + "@Multiplayer, @Online);";
            return DefaultCommand(requete);
        }

        public bool Update()
        {
            var requete = "UPDATE [GameCategory] SET "
                          + "Name=@Name,"
                          + "LongName=@LongName,"
                          + "Multiplayer=@Multiplayer,"
                          + "Online=@Online "
                          + "WHERE Id = " + Id;
            return DefaultCommand(requete);
        }

        private bool DefaultCommand(string requete)
        {
            using (var cnx = new SqlConnection(
                    ConfigurationManager.ConnectionStrings["TP_02"].ConnectionString))
            {

                var cmd = new SqlCommand(requete, cnx) { CommandType = CommandType.Text };

                cmd.Parameters.Add("Name", SqlDbType.NVarChar);
                cmd.Parameters.Add("LongName", SqlDbType.NVarChar);
                cmd.Parameters.Add("Multiplayer", SqlDbType.Bit);
                cmd.Parameters.Add("Online", SqlDbType.Bit);

                cmd.Parameters["Name"].SqlValue = Name;
                cmd.Parameters["LongName"].SqlValue = LongName;
                cmd.Parameters["Multiplayer"].SqlValue = Multiplayer;
                cmd.Parameters["Online"].SqlValue = Online;

                try
                {
                    cnx.Open();
                    cmd.ExecuteNonQuery();
                    cnx.Close();
                    return true;
                }
                catch (Exception e)
                {
                    Debug.WriteLine(e.Message);
                    Debug.WriteLine(e.StackTrace);
                    return false;
                }
            }
        }

        public static Dictionary<int, string> GetDictionary()
        {
            var dict = GetList().ToDictionary(gc => gc.Id, gc => gc.Name);
            dict.Add(0, "Choose Category");
            return dict;
        }


    }
}